﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Business.Compta;
using System.Data.OleDb;
using System.Windows.Media;
using DAO;

namespace DAO.Compta
{
    public class ComptaLinesDAO : IComptaLineNotifier
    {
        private static string ID = MainDAO.ID;
        private static string ID_ACCOUNT = MainDAO.ID_ACCOUNT;
        private static string ID_CATEG = MainDAO.ID_CATEG;
        private static string ID_USER = MainDAO.ID_USER;
        private static string AMOUNT = MainDAO.AMOUNT;
        private static string COMMENT = MainDAO.COMMENT;
        private static string FDATE = MainDAO.FDATE;
        private static string IDATE = MainDAO.IDATE;
        private static string MDATE = MainDAO.MDATE;
        private static string ID_LINE = MainDAO.ID_LINE;
        private static string CATEGORIES = MainDAO.CATEGORIES;
        private static string CATEGORY_COLOR = MainDAO.CATEGORY_COLOR;
        private static string CATEGORY_NAME = MainDAO.CATEGORY_NAME;
        private static string COMPTA_LINE_SUP = MainDAO.COMPTA_LINE_SUP;
        private static string IS_CHECKED = MainDAO.IS_CHECKED;
        private static string IS_PERMANENT = MainDAO.IS_PERMANENT;
        private static string IS_SUP = MainDAO.IS_SUP;


        #region "Lines"
        public static List<ComptaLine> getComptaLines(int idAccount)
        {
            List<ComptaLine> _return = new List<ComptaLine>();

            OleDbConnection conn = MainDAO.OpenComptaConnection();
            OleDbCommand myOleDbCommand2;
            myOleDbCommand2 = new OleDbCommand("SELECT line.ID, line.FDATE, line.AMOUNT, line.COMMENT,line.ID_CATEG, line.IDATE, line.MDATE, line.ID_USER, cat.CATEGORY_NAME, cat.CATEGORY_COLOR" + ", line." + IS_CHECKED + ", line." + IS_PERMANENT + ", line." + IS_SUP +
                                               " from COMPTA_LINE line left outer join CATEGORIES cat ON line.ID_CATEG =  cat.ID " +
                                               " where line.ID_ACCOUNT = @idAccount", conn);
            myOleDbCommand2.Parameters.Add("@idAccount", OleDbType.Integer).Value = idAccount;
            OleDbDataReader myOleDbDataReader = myOleDbCommand2.ExecuteReader();
            int id, idCateg, idUser;
            DateTime date, insertDate, modificationDate;
            Double amount;
            string comment;
            String categoryName, categoryColor;
            bool isChecked, isPermanent, isComptaLineSup;

            while (myOleDbDataReader.Read())
            {
                id = myOleDbDataReader.GetInt32(0);
                date = myOleDbDataReader.GetDateTime(1);
                amount = myOleDbDataReader.GetDouble(2);

                comment = myOleDbDataReader.IsDBNull(3) ? null : myOleDbDataReader.GetString(3);
                idCateg = myOleDbDataReader.IsDBNull(4) ? -1 : myOleDbDataReader.GetInt32(4);
                insertDate = myOleDbDataReader.GetDateTime(5);
                modificationDate = myOleDbDataReader.GetDateTime(6);
                idUser = myOleDbDataReader.IsDBNull(7) ? -1 : myOleDbDataReader.GetInt32(7);
                categoryName = myOleDbDataReader.IsDBNull(8) ? null : myOleDbDataReader.GetString(8);
                categoryColor = myOleDbDataReader.IsDBNull(9) ? null : myOleDbDataReader.GetString(9);
                isChecked = myOleDbDataReader.GetBoolean(10);
                isPermanent = myOleDbDataReader.GetBoolean(11);
                isComptaLineSup = myOleDbDataReader.GetBoolean(12);

                _return.Add(new ComptaLine(id, idAccount, date,
                            idUser,
                            amount, comment,
                            (idCateg == -1) ? null : new Category(idCateg, (categoryName == null) ? "Unknown" : categoryName, ((categoryColor == null) ? Colors.White : (Color)ColorConverter.ConvertFromString(categoryColor))),
                             insertDate, modificationDate, isChecked, isPermanent, isComptaLineSup));
            }
            myOleDbDataReader.Close();
            conn.Close();
            return _return;
        }

        public static void ModifyLine(ComptaLine line)
        {
            OleDbConnection conn = MainDAO.OpenComptaConnection();

            OleDbCommand myCommand = new OleDbCommand();
            myCommand.Connection = conn;
            myCommand.CommandText = "update COMPTA_LINE set ID_USER = @idUser, ID_Categ = @categ, ID_ACCOUNT = @account, AMOUNT = @amount, COMMENT = @comment, FDATE = @fDate, MDATE = @mDate, IS_CHECKED = @isChecked, IS_PERMANENT = @isPermanent, IS_SUP = @isComptaLineSup where ID = @id";

            if (line.IdUser == -1)
                myCommand.Parameters.Add("@idUser", OleDbType.Integer).Value = DBNull.Value;
            else
                myCommand.Parameters.Add("@idUser", OleDbType.Integer).Value = line.IdUser;

            if (line.Category == null || line.Category.IdCategory == -1)
                myCommand.Parameters.Add("@idCateg", OleDbType.Integer).Value = DBNull.Value;
            else
                myCommand.Parameters.Add("@idCateg", OleDbType.Integer).Value = line.Category.IdCategory;

            myCommand.Parameters.Add("@account", OleDbType.Integer).Value = line.IdAccount;
            myCommand.Parameters.Add("@amount", OleDbType.Double).Value = (line.Debit > 0) ? -line.Debit : line.Credit;

            if (line.Comment == null)
                myCommand.Parameters.Add("@comment", OleDbType.VarChar).Value = DBNull.Value;
            else
                myCommand.Parameters.Add("@comment", OleDbType.VarChar).Value = line.Comment;

            myCommand.Parameters.Add("@fdate", OleDbType.Date).Value = line.Date;

            line.ModificationDate = DateTime.Now;
            myCommand.Parameters.Add("@mDate", OleDbType.Date).Value = line.ModificationDate;

            myCommand.Parameters.Add("@isChecked", OleDbType.Boolean).Value = line.IsChecked;
            myCommand.Parameters.Add("@isPermanent", OleDbType.Boolean).Value = line.IsPermanent;
            myCommand.Parameters.Add("@isComptaLineSup", OleDbType.Boolean).Value = line.IsComptaLineSup;

            myCommand.Parameters.Add("@id", OleDbType.Integer).Value = line.Id;

            myCommand.ExecuteNonQuery();
            conn.Close();
        }

        public static int InserNewLine(ComptaLine line)
        {
            OleDbConnection conn = MainDAO.OpenComptaConnection();

            OleDbCommand myCommand = new OleDbCommand();
            myCommand.CommandText = "insert into COMPTA_LINE(ID_USER, ID_Categ, ID_ACCOUNT, AMOUNT, COMMENT, FDATE, IDATE, MDATE, IS_CHECKED, IS_PERMANENT, IS_SUP) VALUES (@idUser, @categ,@account,@amount,@comment, @date, @insertDate, @modificationDate, @isChecked, @isPermanent, @isComptaLineSup)";

            if (line.IdUser == -1)
                myCommand.Parameters.Add("@idUser", OleDbType.Integer).Value = DBNull.Value;
            else
                myCommand.Parameters.Add("@idUser", OleDbType.Integer).Value = line.IdUser;

            if (line.Category == null || line.Category.IdCategory == -1)
                myCommand.Parameters.Add("@idCateg", OleDbType.Integer).Value = DBNull.Value;
            else
                myCommand.Parameters.Add("@idCateg", OleDbType.Integer).Value = line.Category.IdCategory;

            myCommand.Parameters.Add("@account", OleDbType.Integer).Value = line.IdAccount;
            myCommand.Parameters.Add("@amount", OleDbType.Double).Value = (line.Debit > 0) ? -line.Debit : line.Credit;

            if (line.Comment == null)
                myCommand.Parameters.Add("@comment", OleDbType.VarChar).Value = DBNull.Value;
            else
                myCommand.Parameters.Add("@comment", OleDbType.VarChar).Value = line.Comment;

            myCommand.Parameters.Add("@date", OleDbType.Date).Value = line.Date;
            line.InsertDate = DateTime.Now;
            line.ModificationDate = DateTime.Now;
            myCommand.Parameters.Add("@insertDate", OleDbType.Date).Value = line.InsertDate;
            myCommand.Parameters.Add("@modificationDate", OleDbType.Date).Value = line.ModificationDate;

            myCommand.Parameters.Add("@isChecked", OleDbType.Boolean).Value = line.IsChecked;
            myCommand.Parameters.Add("@isPermanent", OleDbType.Boolean).Value = line.IsPermanent;
            myCommand.Parameters.Add("@isComptaLineSup", OleDbType.Boolean).Value = line.IsComptaLineSup;


            OleDbTransaction trans = conn.BeginTransaction();
            myCommand.Transaction = trans;
            myCommand.Connection = conn;
            myCommand.ExecuteNonQuery();
            OleDbCommand myOleDbCommand2;
            myOleDbCommand2 = new OleDbCommand("SELECT @@IDENTITY", conn);
            myOleDbCommand2.Transaction = trans;
            OleDbDataReader myOleDbDataReader = myOleDbCommand2.ExecuteReader();
            myOleDbDataReader.Read();

            int executeInsertGetIdentity = (int)myOleDbDataReader.GetValue(0);
            trans.Commit();
            myOleDbDataReader.Close();
            conn.Close();
            return executeInsertGetIdentity;
        }

        public void InserNewLines(List<ComptaLine> lines)
        {
            OleDbConnection conn = MainDAO.OpenComptaConnection();
            OleDbTransaction trans = null;
            OleDbCommand myOleDbCommand2 = null;
            OleDbDataReader myOleDbDataReader = null;
            OleDbCommand myCommand = new OleDbCommand();
            myCommand.CommandText = "insert into COMPTA_LINE(ID_USER, ID_Categ, ID_ACCOUNT, AMOUNT, COMMENT, FDATE, IDATE, MDATE, IS_CHECKED, IS_PERMANENT, IS_SUP) VALUES (@idUser, @categ,@account,@amount,@comment, @date, @insertDate, @modificationDate, @isChecked, @isPermanent, @isComptaLineSup)";
            double nbLines = lines.Count;
            int i = 0;
            foreach (ComptaLine line in lines)
            {
                myCommand.Parameters.Clear();
                if (line.IdUser == -1)
                    myCommand.Parameters.Add("@idUser", OleDbType.Integer).Value = DBNull.Value;
                else
                    myCommand.Parameters.Add("@idUser", OleDbType.Integer).Value = line.IdUser;

                if (line.Category == null || line.Category.IdCategory == -1)
                    myCommand.Parameters.Add("@idCateg", OleDbType.Integer).Value = DBNull.Value;
                else
                    myCommand.Parameters.Add("@idCateg", OleDbType.Integer).Value = line.Category.IdCategory;

                myCommand.Parameters.Add("@account", OleDbType.Integer).Value = line.IdAccount;
                myCommand.Parameters.Add("@amount", OleDbType.Double).Value = (line.Debit>0)?-line.Debit:line.Credit;

                if (line.Comment == null)
                    myCommand.Parameters.Add("@comment", OleDbType.VarChar).Value = DBNull.Value;
                else
                    myCommand.Parameters.Add("@comment", OleDbType.VarChar).Value = line.Comment;

                myCommand.Parameters.Add("@date", OleDbType.Date).Value = line.Date;
                line.InsertDate = DateTime.Now;
                line.ModificationDate = DateTime.Now;
                myCommand.Parameters.Add("@insertDate", OleDbType.Date).Value = line.InsertDate;
                myCommand.Parameters.Add("@modificationDate", OleDbType.Date).Value = line.ModificationDate;

                myCommand.Parameters.Add("@isChecked", OleDbType.Boolean).Value = line.IsChecked;
                myCommand.Parameters.Add("@isPermanent", OleDbType.Boolean).Value = line.IsPermanent;
                myCommand.Parameters.Add("@isComptaLineSup", OleDbType.Boolean).Value = line.IsComptaLineSup;


                trans = conn.BeginTransaction();
                myCommand.Transaction = trans;
                myCommand.Connection = conn;
                myCommand.ExecuteNonQuery();
                myOleDbCommand2 = new OleDbCommand("SELECT @@IDENTITY", conn);
                myOleDbCommand2.Transaction = trans;
                myOleDbDataReader = myOleDbCommand2.ExecuteReader();
                myOleDbDataReader.Read();
                int executeInsertGetIdentity = (int)myOleDbDataReader.GetValue(0);
                trans.Commit();
                myOleDbDataReader.Close();

                line.Id = executeInsertGetIdentity;

                OnChanged(new NotifyEventArgs((int)(((++i) / nbLines) * 100.0), "Insertion en base de données en cours...", line, NotifyEventArgs.UpdateEnum.Add));
            }

            conn.Close();
        }

        public static int deleteLine(ComptaLine line)
        {
            int _return = 0;
            OleDbConnection conn = MainDAO.OpenComptaConnection();

            //// Read in the file
            OleDbCommand myCommand = new OleDbCommand();

            myCommand.CommandText = "delete from COMPTA_LINE where ID = @id";
            myCommand.Parameters.Add("@id", OleDbType.Integer).Value = line.Id;
            myCommand.Connection = conn;
            _return = myCommand.ExecuteNonQuery();
            conn.Close();
            return _return;
        }

        public List<int> deleteLines(ICollection<ComptaLine> lines)
        {
            List<int> _return = new List<int>();
            OleDbConnection conn = MainDAO.OpenComptaConnection();
            double nbLines = lines.Count;
            int i = 0;

            //// Read in the file
            OleDbCommand myCommand = new OleDbCommand();
            myCommand.CommandText = "delete from COMPTA_LINE where ID = @id";
            myCommand.Connection = conn;
            foreach (ComptaLine line in lines)
            {
                myCommand.Parameters.Clear();
                myCommand.Parameters.Add("@id", OleDbType.Integer).Value = line.Id;
                if (myCommand.ExecuteNonQuery() != 1)
                {
                    _return.Add(line.Id);
                    OnChanged(new NotifyEventArgs((int)(((++i) / nbLines) * 100.0), "Suppression en cours...", null, NotifyEventArgs.UpdateEnum.Del));
                }
                else
                {
                    OnChanged(new NotifyEventArgs((int)(((++i) / nbLines) * 100.0), "Suppression en cours...", line, NotifyEventArgs.UpdateEnum.Del));
                }
            }
            conn.Close();
            return _return;
        }
        #endregion


        #region "Compta Lines Sup"

        public static List<ComptaLineSup> getLinesSup(int idLine)
        {
            List<ComptaLineSup> _return = new List<ComptaLineSup>();

            OleDbConnection conn = MainDAO.OpenComptaConnection();
            OleDbCommand myOleDbCommand2;
            myOleDbCommand2 = new OleDbCommand("SELECT line." +
                ID + "," + ID_LINE + "," + ID_ACCOUNT + "," + ID_CATEG + "," +
                ID_USER + "," + AMOUNT + "," + COMMENT + "," + FDATE + "," + IDATE + "," + MDATE +
                ", cat." + CATEGORY_NAME + ", cat." + CATEGORY_COLOR +
                " from " + COMPTA_LINE_SUP + " line LEFT OUTER JOIN " + CATEGORIES + " cat ON cat." + ID + " = line." + ID_CATEG +
                " where line.ID_LINE = @idLine", conn);
            myOleDbCommand2.Parameters.Add("@idLine", OleDbType.Integer).Value = idLine;
            OleDbDataReader myOleDbDataReader = myOleDbCommand2.ExecuteReader();
            int id, idCateg, idUser, idAccount;
            Double amount;
            string comment, categoryColor, categoryName;
            DateTime fDate, iDate, mDate;

            while (myOleDbDataReader.Read())
            {
                id = myOleDbDataReader.GetInt32(0);
                idLine = myOleDbDataReader.GetInt32(1);
                idAccount = myOleDbDataReader.GetInt32(2);
                idCateg = myOleDbDataReader.IsDBNull(3) ? -1 : myOleDbDataReader.GetInt32(3);
                idUser = myOleDbDataReader.IsDBNull(4) ? -1 : myOleDbDataReader.GetInt32(4);
                amount = myOleDbDataReader.GetDouble(5);
                comment = myOleDbDataReader.IsDBNull(6) ? null : myOleDbDataReader.GetString(6);
                fDate = myOleDbDataReader.GetDateTime(7);
                iDate = myOleDbDataReader.GetDateTime(8);
                mDate = myOleDbDataReader.GetDateTime(9);

                categoryName = myOleDbDataReader.IsDBNull(10) ? null : myOleDbDataReader.GetString(10);
                categoryColor = myOleDbDataReader.IsDBNull(11) ? null : myOleDbDataReader.GetString(11);

                _return.Add(new ComptaLineSup(id, idLine, idAccount, fDate, idUser, amount, comment,
                    (idCateg == -1) ? null : new Category(idCateg, categoryName, (Color)ColorConverter.ConvertFromString(categoryColor)),
                    iDate, mDate));
            }
            myOleDbDataReader.Close();
            conn.Close();
            return _return;
        }

        public static List<ComptaLine> getAllLinesForAccount(int idAccount)
        {
            List<ComptaLine> result = getComptaLines(idAccount);

            List<ComptaLineSup> result2 = getLinesSupForAccount(idAccount);
            result.AddRange(result2.Cast<ComptaLine>());

            return result;
        }

        public static List<ComptaLineSup> getLinesSupForAccount(int idAccount)
        {
            List<ComptaLineSup> _return = new List<ComptaLineSup>();

            OleDbConnection conn = MainDAO.OpenComptaConnection();
            OleDbCommand myOleDbCommand2;
            myOleDbCommand2 = new OleDbCommand("SELECT line." +
                ID + "," + ID_LINE + "," + ID_ACCOUNT + "," + ID_CATEG + "," +
                ID_USER + "," + AMOUNT + "," + COMMENT + "," + FDATE + "," + IDATE + "," + MDATE +
                ", cat." + CATEGORY_NAME + ", cat." + CATEGORY_COLOR +
                " from " + COMPTA_LINE_SUP + " line LEFT OUTER JOIN " + CATEGORIES + " cat ON cat." + ID + " = line." + ID_CATEG +
                " where line." + ID_ACCOUNT + " = @IdAccount", conn);
            myOleDbCommand2.Parameters.Add("@IdAccount", OleDbType.Integer).Value = idAccount;
            OleDbDataReader myOleDbDataReader = myOleDbCommand2.ExecuteReader();
            int id, idCateg, idUser, idLine;
            Double amount;
            string comment, categoryColor, categoryName;
            DateTime fDate, iDate, mDate;

            while (myOleDbDataReader.Read())
            {
                id = myOleDbDataReader.GetInt32(0);
                idLine = myOleDbDataReader.GetInt32(1);
                idAccount = myOleDbDataReader.GetInt32(2);
                idCateg = myOleDbDataReader.IsDBNull(3) ? -1 : myOleDbDataReader.GetInt32(3);
                idUser = myOleDbDataReader.IsDBNull(4) ? -1 : myOleDbDataReader.GetInt32(4);
                amount = myOleDbDataReader.GetDouble(5);
                comment = myOleDbDataReader.IsDBNull(6) ? null : myOleDbDataReader.GetString(6);
                fDate = myOleDbDataReader.GetDateTime(7);
                iDate = myOleDbDataReader.GetDateTime(8);
                mDate = myOleDbDataReader.GetDateTime(9);
                categoryName = myOleDbDataReader.IsDBNull(10) ? null : myOleDbDataReader.GetString(10);
                categoryColor = myOleDbDataReader.IsDBNull(11) ? null : myOleDbDataReader.GetString(11);

                _return.Add(new ComptaLineSup(id, idLine, idAccount, fDate, idUser, amount, comment,
                    (idCateg == -1) ? null : new Category(idCateg, categoryName, (Color)ColorConverter.ConvertFromString(categoryColor)),
                    iDate, mDate));
            }
            myOleDbDataReader.Close();
            conn.Close();
            return _return;
        }

        public static void ModifyLineSup(ComptaLineSup line)
        {
            OleDbConnection conn = MainDAO.OpenComptaConnection();

            OleDbCommand myCommand = new OleDbCommand();
            myCommand.Connection = conn;
            myCommand.CommandText = "update " + COMPTA_LINE_SUP + " set " +
                                    ID_LINE + " = @idLine," +
                                    ID_ACCOUNT + " = @idAccount," +
                                    ID_CATEG + " = @idCateg," +
                                    ID_USER + " = @idUser," +
                                    AMOUNT + " = @amount," +
                                    COMMENT + " = @comment," +
                                    MDATE + " = @mDate" +
                                    " where " + ID + " = @id";
            myCommand.Parameters.Add("@idLine", OleDbType.Integer).Value = line.IdLine;
            myCommand.Parameters.Add("@idAccount", OleDbType.Integer).Value = line.IdAccount;

            if (line.Category == null || line.Category.IdCategory == -1)
                myCommand.Parameters.Add("@idCateg", OleDbType.Integer).Value = DBNull.Value;
            else
                myCommand.Parameters.Add("@idCateg", OleDbType.Integer).Value = line.Category.IdCategory;

            myCommand.Parameters.Add("@idUser", OleDbType.Integer).Value = line.IdUser;
            myCommand.Parameters.Add("@amount", OleDbType.Double).Value = line.Amount;


            if (line.Comment == null)
                myCommand.Parameters.Add("@comment", OleDbType.VarChar).Value = DBNull.Value;
            else
                myCommand.Parameters.Add("@comment", OleDbType.VarChar).Value = line.Comment;

            line.ModificationDate = DateTime.Now;
            myCommand.Parameters.Add("@mDate", OleDbType.VarChar).Value = line.ModificationDate;
            myCommand.Parameters.Add("@id", OleDbType.Integer).Value = line.Id;

            myCommand.ExecuteNonQuery();
            conn.Close();
        }

        public static int InserNewLineSup(ComptaLineSup line)
        {
            OleDbConnection conn = MainDAO.OpenComptaConnection();

            OleDbCommand myCommand = new OleDbCommand();
            myCommand.CommandText = "insert into " + COMPTA_LINE_SUP + "(" +
                ID_LINE + "," + ID_ACCOUNT + "," + ID_CATEG + "," + ID_USER + "," +
                AMOUNT + "," + COMMENT + "," + FDATE + "," + IDATE + "," + MDATE + ") VALUES (@idLine,@idAccount,@idCateg,@idUser, @amount, @comment, @fDate, @iDate, @mDate)";
            myCommand.Parameters.Add("@idLine", OleDbType.Integer).Value = line.IdLine;
            myCommand.Parameters.Add("@idAccount", OleDbType.Integer).Value = line.IdAccount;

            if (line.Category == null || line.Category.IdCategory == -1)
                myCommand.Parameters.Add("@idCateg", OleDbType.Integer).Value = DBNull.Value;
            else
                myCommand.Parameters.Add("@idCateg", OleDbType.Integer).Value = line.Category.IdCategory;

            myCommand.Parameters.Add("@idUser", OleDbType.Integer).Value = line.IdUser;
            myCommand.Parameters.Add("@amount", OleDbType.Double).Value = line.Amount;

            if (line.Comment == null)
                myCommand.Parameters.Add("@comment", OleDbType.VarChar).Value = DBNull.Value;
            else
                myCommand.Parameters.Add("@comment", OleDbType.VarChar).Value = line.Comment;

            myCommand.Parameters.Add("@fDate", OleDbType.VarChar).Value = line.Date;
            line.InsertDate = DateTime.Now;
            line.ModificationDate = DateTime.Now;
            myCommand.Parameters.Add("@iDate", OleDbType.VarChar).Value = line.InsertDate;
            myCommand.Parameters.Add("@mDate", OleDbType.VarChar).Value = line.ModificationDate;


            OleDbTransaction trans = conn.BeginTransaction();
            myCommand.Transaction = trans;
            myCommand.Connection = conn;
            myCommand.ExecuteNonQuery();
            OleDbCommand myOleDbCommand2;
            myOleDbCommand2 = new OleDbCommand("SELECT @@IDENTITY", conn);
            myOleDbCommand2.Transaction = trans;
            OleDbDataReader myOleDbDataReader = myOleDbCommand2.ExecuteReader();
            myOleDbDataReader.Read();

            int executeInsertGetIdentity = (int)myOleDbDataReader.GetValue(0);
            trans.Commit();
            myOleDbDataReader.Close();
            conn.Close();
            return executeInsertGetIdentity;


        }

        public static int deleteLineSup(ComptaLineSup line)
        {
            int _return = 0;
            OleDbConnection conn = MainDAO.OpenComptaConnection();

            //// Read in the file
            OleDbCommand myCommand = new OleDbCommand();

            myCommand.CommandText = "delete from " + COMPTA_LINE_SUP + " where " + ID + " = @id";
            myCommand.Parameters.Add("@id", OleDbType.Integer).Value = line.Id;
            myCommand.Connection = conn;
            _return = myCommand.ExecuteNonQuery();
            conn.Close();
            return _return;
        }
        #endregion


        //public static System.Data.DataSet importFromXls(string file)
        //{
        //    System.Data.OleDb.OleDbConnection myConnection = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source=" + file + ";Extended Properties=Excel 8.0;");
        //    System.Data.OleDb.OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
        //    System.Data.DataSet ds = new System.Data.DataSet();
        //    try
        //    {
        //        myCommand.Fill(ds);
        //        return ds;
        //    }
        //    catch (Exception ex)
        //    {
        //        Console.WriteLine("Erreur de connection au fichier\n{0}", ex.Message);
        //    }
        //    finally
        //    {
        //        myConnection.Close();
        //    }
        //    return null;
        //}
    }
}
